package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTMonitorFaultDto;
import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.MonitorFaultDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by ding on 2017/8/4.
 */
public class MonitorFaultRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public List<DTMonitorFaultDto> getMonitorFaultCheck4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT MonitorFaultId,mf.MonitorId,mf.MonitorFaultPhoto,mf.MonitorFaultCreatorId,mf.MonitorFaultTime,mf.MonitorCheckerId," +
                "mf.MonitorMoney,mf.MonitorFaultLevel,mf.MonitorFaultType,mf.MonitorMainCheckerId,mf.MonitorFeedback,mf.MonitorFaultReason,\n" +
                "mf.MonitorMainCheck,mf.MonitorCheck,mf.MonitorOrgId,mf.MonitorMainOrgId,OrgName AS monitorOrgName,m.MonitorPhoto \n" +
                "FROM tb_monitor_fault mf,tb_monitor m,tb_org o\n" +
                "WHERE mf.MonitorOrgId=o.OrgId AND mf.MonitorId = m.MonitorId   " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.MonitorCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " and mf.MonitorMainOrgId=" + orgId + "";
        }
        sql += "  AND  mf.MonitorMainCheck IN (0,1,-1) \n" +
                " order by  find_in_set( mf.MonitorMainCheck ,'0,1,-1') , mf.MonitorFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTMonitorFaultDto.class));
        List<DTMonitorFaultDto> lampFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return lampFaultDtos;
    }

    public Integer getMonitorFaultCheckCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_monitor_fault mf,tb_monitor m,tb_org o\n" +
                "WHERE mf.MonitorOrgId=o.OrgId AND mf.MonitorId = m.MonitorId   " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.MonitorCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " and mf.MonitorMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTMonitorFaultDto> getMonitorFaultHandle4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer monitorMaintenanceStatus) throws Exception {
        String sql = "SELECT lf.*,OrgName AS monitorOrgName,sl.MonitorLongitude,sl.MonitorLatitude,sl.MonitorStartRoad,sl.MonitorEndRoad,sl.MonitorRoadName," +
                "u.userName as monitorFaultCreatorName,sl.MonitorPhoto,sl.MonitorArea,sl.MonitorCity,sl.MonitorProvince FROM tb_monitor sl,tb_monitor_fault lf,tb_org o,tb_user u\n" +
                "WHERE sl.MonitorId=lf.MonitorId AND lf.MonitorOrgId=o.OrgId AND u.userId = lf.monitorFaultCreatorId AND lf.MonitorMainCheck in (1,2) \n" +
                "AND concat(sl.MonitorStartRoad, sl.MonitorEndRoad,sl.MonitorRoadName) like '%" + dtRequestDto.getSearch() + "%' AND lf.MonitorCheck in (1,2) ";
        //根据维修人员和系统管理员区分
        if (monitorMaintenanceStatus != 3) {
            sql += " and lf.monitorMaintenanceStatus = " + monitorMaintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND lf.MonitorOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND lf.MonitorMainOrgId =" + orgId + "";
        }

        sql += " order by lf.monitorMaintenanceStatus ASC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTMonitorFaultDto.class));
        List<DTMonitorFaultDto> solarFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return solarFaultDtos;
    }

    public Integer getMonitorFaultHandleCount4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer monitorMaintenanceStatus) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_monitor sl,tb_monitor_fault lf,tb_org o\n" +
                "WHERE sl.MonitorId=lf.MonitorId AND lf.MonitorOrgId=o.OrgId AND lf.MonitorMainCheck=1 \n" +
                "AND concat(sl.MonitorStartRoad, sl.MonitorEndRoad,sl.MonitorRoadName) like '%" + dtRequestDto.getSearch() + "%' AND lf.MonitorCheck=1 ";
        //根据维修人员和系统管理员区分
        if (monitorMaintenanceStatus != 3) {
            sql += " and lf.monitorMaintenanceStatus = " + monitorMaintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND lf.MonitorOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND lf.MonitorMainOrgId =" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<MonitorFaultDto> getMonitorFaultCheckList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = " SELECT lf.MonitorFaultId,lf.MonitorId,lf.MonitorFaultPhoto,lf.MonitorFaultType,lf.MonitorFaultLevel,lf.MonitorFaultReason," +
                "lf.MonitorFaultTime,lf.MonitorMainCheck,lf.MonitorCheck,lf.MonitorMainCheckTime\n" +
                "FROM tb_monitor_fault lf ";
        if (actorIds.indexOf("1") >= 0) {//系统管理员
            if (faultStatus == 0) {//待审核
                sql += " WHERE (lf.MonitorCheck =0) OR (lf.MonitorMainCheck =0 AND lf.MonitorCheck=1 )  ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE  lf.MonitorMainCheck =1 AND lf.MonitorCheck=1  ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE ( lf.MonitorCheck =-1) OR (lf.MonitorMainCheck =-1 AND lf.MonitorCheck=1 ) ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("3") >= 0) {//审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE lf.MonitorCheck=0 AND lf.MonitorOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE lf.MonitorCheck=1 AND lf.MonitorOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE lf.MonitorCheck=-1 AND lf.MonitorOrgId=" + orgId + " ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("7") >= 0) {//主管审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE lf.MonitorCheck=1 AND lf.MonitorMainCheck=0 AND lf.MonitorMainOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE lf.MonitorCheck=1 AND lf.MonitorMainCheck=1 AND lf.MonitorMainOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE lf.MonitorCheck=1 AND lf.MonitorMainCheck=-1 AND lf.MonitorMainOrgId=" + orgId + " ";
            } else {
                return null;
            }
        }
        sql += " ORDER BY lf.MonitorFaultTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonitorFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public Integer getMonitorReportFaultCount4DT(Integer actorId, Integer userId, Integer orgId, String strSearch) throws Exception {
        String sql = "select count(*) " +
                "FROM (((tb_monitor_fault mf JOIN tb_user u ON u.UserId=mf.MonitorFaultCreatorId)\n" +
                "JOIN tb_org o ON o.OrgId=mf.MonitorOrgId)JOIN tb_monitor m ON m.MonitorId=mf.MonitorId)\n" +
                "WHERE concat(m.MonitorRoadName,m.MonitorStartRoad,m.MonitorEndRoad)like '%" + strSearch + "%'";
        if (actorId == 3) {
            sql += " and mf.MonitorOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and mf.MonitorFaultCreatorId=" + userId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTMonitorFaultDto> getMonitorReportFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "SELECT mf.*,u.UserName AS monitorFaultCreatorName,u2.UserName AS monitorMaintainerName,u3.UserName " +
                "AS monitorMainCheckerName, o.OrgName AS monitorOrgName,m.MonitorPhoto from tb_monitor_fault mf left join tb_user u on" +
                " u.UserId=mf.MonitorFaultCreatorId left join tb_user u2 on u2.userId = mf.MonitorMaintainerId " +
                "left join tb_user u3 on u3.userId=mf.MonitorMainCheckerId left join tb_org o on o.orgId=mf.MonitorOrgId " +
                "left join tb_monitor m on m.MonitorId=mf.MonitorId " +
                "WHERE concat(m.MonitorRoadName,m.MonitorStartRoad,m.MonitorEndRoad)like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3) {
            sql += " and mf.MonitorOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and mf.MonitorFaultCreatorId=" + userId + "";
        }
        sql += " AND mf.MonitorCheck IN (0,1,2,-1) \n" +
                " order by find_in_set( mf.MonitorCheck ,'0,2,1,-1') , mf.MonitorFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTMonitorFaultDto.class));
        List<DTMonitorFaultDto> monitorFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return monitorFaultDtos;
    }


    public List<MonitorFaultDto> getMonitorFaultReportList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = "SELECT lf.MonitorFaultPhoto,lf.MonitorId,lf.MonitorFaultType,lf.MonitorFaultReason,lf.MonitorFaultId,lf.MonitorFaultLevel,lf.MonitorFaultTime,lf.MonitorCheck,lf.MonitorMainCheck\n" +
                "FROM tb_monitor sl,tb_monitor_fault lf\n" +
                "WHERE sl.MonitorId=lf.MonitorId\n";
        //-1:未通过；0：待审核；1：已通过
        if (faultStatus == -1) {
            sql += " AND (lf.MonitorCheck=-1 OR lf.MonitorMainCheck=-1) ";
        } else if (faultStatus == 0) {
            sql += " AND ((lf.MonitorCheck=1 AND lf.MonitorMainCheck=0) OR lf.MonitorCheck = 0) ";
        } else if (faultStatus == 1) {
            sql += " AND lf.MonitorMainCheck = 1 ";
        }
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += " AND lf.MonitorOrgId = " + orgId + " ";
        }
        sql += "ORDER BY MonitorFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonitorFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<MonitorFaultDto> getMonitorFaultHandleList4App(Integer orgId, Integer maintenanceStatus, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT mf.MonitorFaultId,mf.MonitorId,mf.MonitorFaultPhoto,mf.MonitorFaultType,mf.MonitorFaultLevel," +
                "mf.MonitorFaultReason,mf.MonitorMaintenanceStatus,mf.MonitorMainCheckTime,mf.MonitorMaintenancePhoto," +
                "mf.MonitorMaintenanceTime\n" +
                "FROM tb_monitor_fault mf " +
                "WHERE mf.MonitorMaintenanceStatus=" + maintenanceStatus + "";
        if (actorIds.indexOf("7") >= 0) {
            sql += " AND mf.MonitorMainOrgId=" + orgId + "";
        } else if (actorIds.indexOf("5") >= 0) {
            sql += " AND mf.MonitorOrgId=" + orgId + "";
        }
        sql += " ORDER BY mf.MonitorMainCheckTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonitorFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }
}
